﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Deanery.Controller.Directories;
using System.Collections;

namespace Deanery.Controller.StudyData
{
    class NapryamController
    {
        private readonly deaneryEntities _db = new deaneryEntities();

        /// <summary>
        /// This method transfers columns name in ukrainian for view table Napryam
        /// </summary>
        /// <returns>Array of string with columns names</returns>
        public Array ColumnName()
        {
            string[] columnName = { "ID", "Код напряму", "Напрям", "Код спеціальності", "Спеціальність", "Код спеціалізації", "Спеціалізація", "Професійні права", "Факультет", "Клод ЄДБО", "ОКР" };
            return columnName;
        }

        /// <summary>
        /// This method transfers columns data type in table Napryam
        /// </summary>
        /// <returns>Array of string with columns data type</returns>
        public Array ColumnValueType()
        {
            string[] columnDataType = { "guid", "string", "string", "string", "string", "string", "string", "string", "guid", "string", "guid"};
            return columnDataType;
        }

        /// <summary>
        /// This method select all reccords from table Napryam
        /// PROBLEM: if will be many reccords in this table, method can slove the application
        /// SOLVE: stable quantities assync selection 
        /// </summary>
        /// <returns>Set of IQueryable</returns>
        public IQueryable GetAll()
        {
            var list = from t in _db.Napryams
                       join f in _db.Faculties on t.id_faculty equals f.id_faculty
                       join h in _db.Qualifications on t.id_qualification equals h.id_qualification
                       select new
                       {
                           t.id_napryam,
                           t.nv_code_naprym,
                           t.nv_napryam_name,
                           t.nv_code_specialty,
                           t.nv_specialty_name,
                           t.nv_code_specialization,
                           t.nv_specialization_name,
                           t.nv_prof_prava,
                           f.nv_faculty_name,
                           t.guid_edbo,
                           h.nv_qualification_name
                       };
            return list;
        }

        /// <summary>
        /// This method select all faculty_name name from table Faculty
        /// idea of this method: to return data from all conected tables with this one
        /// </summary>
        /// <returns>Array(every item is test name)</returns>
        public Array GetConectedDataFaculty()
        {
            var list = from t in _db.Faculties select t.nv_faculty_name;
            return list.ToArray();
        }

        /// <summary>
        /// This method select all qualification_name name from table Qualificatio
        /// idea of this method: to return data from all conected tables with this one
        /// </summary>
        /// <returns>Array(every item is test name)</returns>s
        public Array GetConectedDataQualification()
        {
            var list = from t in _db.Qualifications select t.nv_qualification_name;
            return list.ToArray();
        }


        /// <summary>
        /// This method performs INSERT one record to DB in table Napryam
        /// PROBLEM: method returns nothing
        /// </summary>
        /// <param name="reccord">ArrayList(one item - one cell value) with data to insert</param>
        public void Insert(ArrayList reccord)
        {
            var typeFacultyObj = new FacultyController();
            var typeQualificationObj = new QualificationController();

            var obj = new Napryam
            {
                id_napryam = GenerateGuid(),
                nv_code_naprym = reccord[0].ToString(),
                nv_napryam_name= reccord[1].ToString(),
                nv_code_specialty = reccord[2].ToString(),
                nv_specialty_name = reccord[3].ToString(),
                nv_code_specialization = reccord[4].ToString(),
                nv_specialization_name = reccord[5].ToString(),
                nv_prof_prava = reccord[6].ToString(),
                id_faculty = typeFacultyObj.FindGuidByName(reccord[7].ToString()),
                guid_edbo = reccord[8].ToString(),
                id_qualification = typeQualificationObj.FindGuidByName(reccord[9].ToString())

            };
            _db.Napryams.AddObject(obj);
            _db.SaveChanges();
        }

        /// <summary>
        /// This method performs UPDATE one record to DB in table Napryam
        /// PROBLEM: method returns nothing
        /// </summary>
        /// <param name="updateGuid">guid value to search reccord in table</param>
        /// <param name="reccord">ArrayList(one item - one cell value) with data to update</param>
        public void Update(Guid updateGuid, ArrayList reccord)
        {
            var typeFacultyObj = new FacultyController();
            var typeQualificationObj = new QualificationController();

            var obj = (from t in _db.Napryams where t.id_napryam == updateGuid select t).Single();
            
                obj.nv_code_naprym = reccord[0].ToString();
                obj.nv_napryam_name= reccord[1].ToString();
                obj.nv_code_specialty = reccord[2].ToString();
                obj.nv_specialty_name = reccord[3].ToString();
                obj.nv_code_specialization = reccord[4].ToString();
                obj.nv_specialization_name = reccord[5].ToString();
                obj.nv_prof_prava = reccord[6].ToString();
                obj.id_faculty = typeFacultyObj.FindGuidByName(reccord[7].ToString());
                obj.guid_edbo = reccord[8].ToString();
                obj.id_qualification = typeQualificationObj.FindGuidByName(reccord[9].ToString());
           
            _db.SaveChanges();
        }
        /// <summary>
        /// This method generates new guid
        /// </summary>
        /// <returns>New guid</returns>
        public Guid GenerateGuid()
        {
            var guid = Guid.NewGuid();
            return guid;
        }

        /// <summary>
        /// This method select napryams name from table Napryam
        /// </summary>
        /// <returns>Array(every item is qualifications name)</returns>

        public Array GetNapryamName(Guid qualification)
        {
            var list = from t in _db.Napryams
                       where t.id_qualification == qualification
                       select (t.nv_code_naprym + " " + t.nv_napryam_name);

            return list.ToArray();
        }

    }
}
